package com.chinawayltd.ews.sample.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.chinawayltd.ews.sample.core.exception.BusinessException;
import com.chinawayltd.ews.sample.model.TBranch;
import com.chinawayltd.ews.sample.util.PageData;
import com.chinawayltd.ews.sample.util.ReflectionsUtils;

/**
 * @program: g7ews4java-sample
 * @description: ${description}
 * @author: Mr.yuan
 * @create: 2018-04-12 16:56
 **/
public class ExcelUtils {

    private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 解析导入excle文件
     * @param file
     * @return 多个sheet页数据 格式为（sheet > row > cell）
     */
    public static ExcelResultModel analyzeExcel(MultipartFile file) {

        ExcelResultModel model = new ExcelResultModel();

        String fileName = file.getOriginalFilename();

        if(!fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            //不是excel文件
            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_NOT_FILE);
        }

        long fileSize = file.getSize();

        if(fileSize <= 0) {
            //不是有效的文件，无文件内容。
            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_FILE_SIZE_ZERO);
        }

        boolean is03Excell = fileName.matches("^.+\\.(?i)(xls)$")?true:false;

        try {
            POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
            Workbook workbook = is03Excell ? new HSSFWorkbook(fs):new XSSFWorkbook(file.getInputStream());

            int sheetNumber = workbook.getNumberOfSheets();

            if(sheetNumber < 0) {
                //excel没有内容
                model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_FILE_SHEET_NOT_CONTENT);
            }

            List<List<String []>> sheetList = new ArrayList<>();

            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = workbook.getSheetAt(i);

                List<String []> rowList = new ArrayList<>();
                int rowNumber = sheet.getLastRowNum();
                for (int j = 1; j <= rowNumber; j++) {
                    Row row = sheet.getRow(j);

                    int cellNumber = row.getLastCellNum();
                    String [] cells = new String[cellNumber];
                    for (int k = 0; k < cellNumber; k++) {
                        Cell cell = row.getCell(k);
                        cells[k] = getColumnValue(cell);
                    }

                    rowList.add(cells);
                }
                sheetList.add(rowList);

            }

            model.setSheetList(sheetList);
            return model;

        } catch (IOException e) {
            e.printStackTrace();
            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_ERROT);
            return model;
        }

    }

    public static Workbook generateExcel(ExcelGenerateModel model){
        List<ExportCloumsModel> cloumnList = model.getCloumnList();
        List<Object> dataList = model.getDataList();

        if(model == null){
            throw new RuntimeException("model must be not null!");
        }else if(cloumnList == null || cloumnList.isEmpty()){
            throw new RuntimeException("model.cloumnList must be not null or empty!");
        }else if(dataList == null){
            throw new RuntimeException("model.dataList must be not null!");
        }

        Workbook workbook = model.is03Excel() ? new HSSFWorkbook() : new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(model.getSheetName());
        sheet.setDefaultColumnWidth(20);
        createSheetTitleRow(cloumnList, sheet, workbook);

        //把数据插入到excel
        for (int dataIdx = 0; dataIdx < dataList.size(); dataIdx++) {
            Object data =  dataList.get(dataIdx);
            Row row = sheet.createRow(dataIdx + 1);
            if(data instanceof Map){
                Map<String, Object> dataMap = (Map<String, Object>)data;
                for (int cloumnIdx = 0; cloumnIdx < cloumnList.size(); cloumnIdx++) {
                    ExportCloumsModel cloumn =  cloumnList.get(cloumnIdx);
                    Object valueObj = dataMap.get(cloumn.getField());
                    if(valueObj != null){
                        row.createCell(cloumnIdx).setCellValue(valueObj.toString());
                    }
                }
            }else{
                for (int cloumnIdx = 0; cloumnIdx < cloumnList.size(); cloumnIdx++) {
                    ExportCloumsModel cloumn =  cloumnList.get(cloumnIdx);
                    Object valueObj = ReflectionsUtils.invokeGetter(data, cloumn.getField());
                    if(valueObj != null){
                        row.createCell(cloumnIdx).setCellValue(valueObj.toString());
                    }
                }
            }
        }
        return  workbook;
    }

    /**
     * 生成sheet页的title行
     * @param cloumnList
     * @param sheet
     * @param workbook
     */
    private static void createSheetTitleRow(List<ExportCloumsModel> cloumnList, Sheet sheet, Workbook workbook){
        Row row = sheet.createRow(0);
        for (int i = 0; i < cloumnList.size(); i++) {
            ExportCloumsModel cloumnModel =  cloumnList.get(i);
            Cell cell = row.createCell(i);
            cell.setCellValue(cloumnModel.getTitle());
            cell.setCellStyle(getTitleCellStyle(workbook));
        }

    }

    /**
     * 生成抬头的单元格样式
     * @param workbook
     * @return
     */
    private static CellStyle getTitleCellStyle(Workbook workbook){
        CellStyle style = workbook.createCellStyle();
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);

        Font font = workbook.createFont();
//        font.setFontHeight((short)16);
        font.setBold(true);
        style.setFont(font);
        return style;
    }
    
    /**
     * 解析excel2007
     * @author: chenxin
     * @date: 2018年4月24日
     * @param filepath // 文件路径
     * @param filename // 文件名
     * @param startrow // 开始行号
     * @param startcol // 开始列号
     * @param sheetnum // sheet
     * @return
     */
	public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<Object>();
		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			HSSFSheet sheet = wb.getSheetAt(sheetnum); 					//sheet 从0开始
			int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号
			for (int i = startrow; i < rowNum; i++) {					//行循环开始
				PageData varpd = new PageData();
				HSSFRow row = sheet.getRow(i); 							//行
				int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置
				for (int j = startcol; j < cellNum; j++) {				//列循环开始
					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						switch (cell.getCellType()) { 					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						case 0:
							cellValue = String.valueOf((int) cell.getNumericCellValue());
							break;
						case 1:
							cellValue = cell.getStringCellValue();
							break;
						case 2:
							cellValue = cell.getNumericCellValue() + "";
							// cellValue = String.valueOf(cell.getDateCellValue());
							break;
						case 3:
							cellValue = "";
							break;
						case 4:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case 5:
							cellValue = String.valueOf(cell.getErrorCellValue());
							break;
						}
					} else {
						cellValue = "";
					}
					varpd.put("var"+j, cellValue);
				}
				varList.add(varpd);
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return varList;
	}
	
	/**
	 * 解析excel
	 * @author: chenxin
	 * @date: 2018年4月24日 下午9:42:40
	 * @param file
	 * @return
	 */
	public static ExcelObjModel readExcel(MultipartFile file) {
		ExcelObjModel model = new ExcelObjModel();
		model.setCode(0);
		try {
			// 不是excel文件
			if (file == null && file.isEmpty()) {
				model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_NOT_FILE);
				model.setMsg("导入文件为空");
	            return model;
			}
			String fileName = file.getOriginalFilename();
			// 不是excel文件
			if(!fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
	            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_NOT_FILE);
	            model.setMsg("导入的表不是excel文件");
	            return model;
	        }
			long fileSize = file.getSize();
			// 不是有效的文件，无文件内容。
	        if(fileSize <= 0) {
	            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_FILE_SIZE_ZERO);
	            model.setMsg("导入的表不是有效的文件，无文件内容");
	            return model;
	        }
			String fileType=fileName.substring(fileName.lastIndexOf(".")+1); 
			List<Object> varList = null;
			if("xls".equalsIgnoreCase(fileType)){
				varList = importExcel2003(file);
			}else{ 
				varList = importExcel2007(file); 
			}
            model.setList(varList);
            return model;
		} catch (BusinessException e) {
			 model.setCode(400);
	         model.setMsg("导入的表不是有效的文件或数据为空");
	         return model;
        } catch (Exception e) {
            model.setCode(ExcelResultModel.constant.IMPORT_EXCEL_ERROT);
            model.setMsg("导入的表不是有效的文件");
            return model;
        }
	}

	/**
	 * 遍历excel2007
	 * @author: chenxin
	 * @date: 2018年4月24日 下午9:44:13
	 * @param file
	 * @return
	 * @throws IOException
	 */
	private static List<Object> importExcel2007(MultipartFile file) throws IOException {
		List<Object> list = new ArrayList<>();
		InputStream in = file.getInputStream();
		XSSFWorkbook wb = new XSSFWorkbook(in);
		int sheetNumber = wb.getNumberOfSheets();
		if (sheetNumber < 0) {
			if(in !=null)in.close();
			throw new BusinessException("导入的表不是有效的文件，无文件内容");
		}
		XSSFSheet sheet = wb.getSheetAt(0);
		int rows = sheet.getPhysicalNumberOfRows();
		if(rows <= 1){
			throw new BusinessException("导入的表不是有效的文件或数据为空");
		}
		XSSFRow row = sheet.getRow(0);
		PageData varpd = null;
		for (int i = 1; i < rows; i++) {
			row = sheet.getRow(i);
			int cells = row.getLastCellNum();
			int blankRow = CheckXSSFRowNull(row);
            if(blankRow >=cells-1){// 检查每行的空格数，如果大于2固定为空值整行
                continue;
            }
            varpd = new PageData();
			for (int j = 0; j < cells; j++) {
				XSSFCell cell = row.getCell(j);
				String cellValue = null;
				if (cell != null) {
					cellValue = getColumnValue(cell);
				} else {
					cellValue = "";
				}
				varpd.put("var"+j, cellValue);
			}
			if(varpd !=null){
				list.add(varpd);
			}
		}
		if(in !=null)in.close();
		return list;
	}

	/**
	 * 遍历excel2003
	 * @author: chenxin
	 * @date: 2018年4月48日 下午9:44:13
	 * @param file
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings({ "deprecation", "static-access" })
	private static List<Object> importExcel2003(MultipartFile file) throws Exception {
		List<Object> list = new ArrayList<>();
		POIFSFileSystem in = new POIFSFileSystem(file.getInputStream());
		HSSFWorkbook wb = new HSSFWorkbook(in);
		int sheetNumber = wb.getNumberOfSheets();
		if (sheetNumber < 0) {//没有sheets页
			if(in !=null)in.close();
			throw new BusinessException("导入的表不是有效的文件，无文件内容");
		}
		HSSFSheet sheet = wb.getSheetAt(0); 					//sheet 从0开始
		int rowNum = sheet.getLastRowNum() + 1; 				//取得最后一行的行号
		if(rowNum <= 1){
			throw new BusinessException("导入的表不是有效的文件或数据为空");
		}
		PageData varpd =null;
		for (int i = 1; i < rowNum; i++) {					//行循环开始
			HSSFRow row = sheet.getRow(i);						//行
			int blankRow = CheckHSSFRowNull(row);
			int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置
			if(blankRow >=cellNum-1){// 检查每行的空格数
                continue;
            }
			varpd = new PageData();
			int isemEmpty = 0;
			for (int j = 0; j < cellNum; j++) {				//列循环开始
				HSSFCell cell = row.getCell(Short.parseShort(j + ""));
				String cellValue = null;
				if (cell !=null) {
					cellValue = getColumnValue(cell);
					if(cellValue ==null) isemEmpty++;
				} else {
					cellValue = "";
					isemEmpty++;
				}
				varpd.put("var"+j, cellValue);
			}
			if(varpd !=null && isemEmpty!=cellNum){
				list.add(varpd);
			}
		}
		if(in !=null)in.close();
		return list;
	}
	
    /**
     * 解析excel列，根据列类型转换为String
     * @param cell
     * @return
     */
    private static String getColumnValue(Cell cell){
        CellType cellType = cell.getCellTypeEnum();
        String temp = "";
        switch (cellType) {
            case _NONE:
                break;
            case STRING:
                temp = cell.getStringCellValue().trim();
                //temp = StringUtils.isEmpty(temp) ? "NULL" : temp;
                break;
            case FORMULA:
                temp = String.valueOf(cell.getCellFormula().trim());
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    //temp = df.format(cell.getDateCellValue());
                    short format = cell.getCellStyle().getDataFormat();
                    //System.out.println("format:"+format+";;;;;value:"+cell.getNumericCellValue());
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58  
                            || (176<=format && format<=178) || (182<=format && format<=196) 
                            || (210<=format && format<=213) || (208==format ) ) { // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    } else if (format == 20 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
                        sdf = new SimpleDateFormat("HH:mm");
                    } else { // 不是日期格式
                        return String.valueOf(cell.getNumericCellValue());
                    }
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    if(date==null || "".equals(date)){
                        return "";
                    }
                    try {
                    	temp = sdf.format(date);
                    } catch (Exception e) {
                        e.printStackTrace();
                        temp = "";
                    }
                } else {
                    temp = new DecimalFormat("#.######").format(cell.getNumericCellValue()).trim();
                }
                break;
            case BOOLEAN:
                temp = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                temp = null;
                break;
            case ERROR:
                temp = "ERROR";
                break;
            default:
                temp = cell.toString().trim();
                break;
        }
        return temp;
    }
	//判断行为空  
	@SuppressWarnings("deprecation")
	private static int CheckHSSFRowNull(HSSFRow hssfRow) {
		int num = 0;
		Iterator<Cell> cellItr = hssfRow.iterator();
		while (cellItr.hasNext()) {
			Cell c = cellItr.next();
			if (c.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				num++;
			}
		}
		return num;
	}  
	//判断行为空  
	@SuppressWarnings("deprecation")
	private static int CheckXSSFRowNull(XSSFRow hssfRow) {
		int num = 0;
		Iterator<Cell> cellItr = hssfRow.iterator();
		while (cellItr.hasNext()) {
			Cell c = cellItr.next();
			if (c.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
				num++;
			}
		}
		return num;
	}
	
    public static void main(String[] args) throws Exception {
        ArrayList<TBranch> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            TBranch o = new TBranch();
            o.setBranchCode("test" + i);
            o.setBranchName("testName" + i);
            dataList.add(o);
        }

        ExcelGenerateModel excelModel = new ExcelGenerateModel.Builder(dataList)
                .putCloumn("网点编号", "branchCode").putCloumn("网点名称","branchName").build();
        Workbook workbook = ExcelUtils.generateExcel(excelModel);

        FileOutputStream fout = new FileOutputStream("D:tesd.xls");
        workbook.write(fout);
    }
}
